package manager;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.List;

import javax.faces.model.SelectItem;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;

import constants.Globals;
import domain.DadServiceParametersDAO;

import structures.kys.KysTableDeployInfo;
import util.HibernateUtil;

import analyzers.InputAnalyzer;

import exception.ExceptionStringHandler;
import exception.IBToolsException;

public class KysManager {

	public static final String LOGIN_URL = "CMS_LOGIN_CONTROL.P_Login_Control"; 
	public static final String SERVICE_URL = "DAD_SERVICE.P_Service_Entry"; 
	public static final String SERVICE_PARAMETER_URL = "DAD_SERVICE.P_Service_Parameter_Entry"; 
	public static final String DELPOY_ENTRY_VERSION_URL = "CMS_DEPLOY_ENTRY.P_NewVersion_Region"; 
	public static final String TABLE_DEPLOY_URL = "CMS_TABLE_ENTRY.P_Table_Deploy_Entry"; 
	public static final String PROJECT_REGION_URL = "CMS_COMMON_REGIONS.P_Project_Region";
	public static final String QUERY_PARAMETER_URL = "DAD_QUERY.P_Query_Parameter_Entry";
	public static final String COMPONENT_DEPLOY_URL = "CMS_DEPLOY_ENTRY.P_Deploy_Entry";

	public static void main(String[] args) throws Exception {
		int a[][] = new int[2][2];
//		System.out.print(a.length);
		System.out.println(java.lang.System.getProperty("java.library.path"));
	}

	public static String quickUpdateServiceBaseParameter(Long serviceOid, String name, String type, boolean isInput, Long parentOid, Long parameterOid) throws IBToolsException{
		return saveServiceParameter(false, "", "", "", "", serviceOid, isInput, true, name, type, parentOid, parameterOid, false);
	}
	
	public static String quickSaveServiceBaseParameter(Long serviceOid, String name, String type, boolean isInput, Long parentOid) throws IBToolsException{
		return saveServiceParameter(false, "", "", "", "", serviceOid, isInput, true, name, type, parentOid, null, false);
	}
	
	public static String saveServiceParameter(boolean isArray, String businessType, String description,
			String domain, String domainType, Long serviceOid, boolean isInput, boolean isOptional,
			String name, String type, Long parentOid, Long parameterOid, boolean pureHtmlResponse) throws IBToolsException{
		
		/***
		 * p_arr:
		 * p_businesstype:
		 * p_description:
		 * p_domain:
		 * p_domaintype:
		 * p_id:
		 * p_id_old:
		 * p_iotype:
		 * Ip_mainid:1881000002751033
		 * p_optional:0
		 * p_paramname:
		 * p_paramtype:
		 * p_parentoid:
		 * p_process_type:ADD
		 * p_userid:91030202
		 * p_xsd_encrypted:
		 * p_xsd_ignorable:
		 * 
		    UPDATE
		    p_parentoid:
			p_paramname:TEST
			p_paramtype:STRING
			p_domaintype:
			p_arr:0
			p_iotype:I
			p_optional:1
			p_domain:
			p_businesstype:
			p_description:IBTools(T32556) - TEST
			p_xsd_encrypted:0
			p_xsd_ignorable:0
			p_userid:91030202
			p_process_type:SAV
			p_id:1881000002767910
			p_mainid:1881000002725464
			p_id_old:1881000002767910
		 */
		
		if(description.indexOf(SessionManager.getLoginUserCode()) < 0)
			description = "IBT(" + SessionManager.getLoginUserCode() + ") - " + (description == null || description.equals("") ? name : description) ;
		
		String parameters = generateParameterLink(isArray, businessType, description, domain, domainType, 
				serviceOid, isInput, isOptional, name, type, parentOid, parameterOid, "SAV", pureHtmlResponse);
		
		return postUrl(name, "https://kysprd.finansbank.com.tr/PRD/dep/" + SERVICE_PARAMETER_URL,
				parameters, pureHtmlResponse);
	}
	public static String getServicePage(String groupName, Long componentOid, Long serviceOid) throws IBToolsException{
		
		String parameters = generateServiceLink(groupName, componentOid, serviceOid);
		String postUrl = postUrl(null, "https://kysprd.finansbank.com.tr/PRD/dep/" + SERVICE_URL,
				parameters, true)
				.replaceAll("/PRD/dep", "https://kysprd.finansbank.com.tr/PRD/dep")
				.replaceAll("/images", "https://kysprd.finansbank.com.tr/images");
		int htmlIndex = postUrl.indexOf("<HTML");
		if(htmlIndex == -1)
			htmlIndex = postUrl.indexOf("<html");
		postUrl = postUrl.substring(htmlIndex);
		int formStartIndex = postUrl.indexOf("<FORM");
		if(formStartIndex == -1)
			formStartIndex = postUrl.indexOf("<form");
		int formEndIndex = postUrl.lastIndexOf("</form>");
		if(formEndIndex == -1)
			formEndIndex = postUrl.lastIndexOf("</FORM>");
		String subString = postUrl.substring(formStartIndex, formEndIndex + 7);
		postUrl = postUrl.replaceAll(subString, "");
		return postUrl;
	}
	public static String getServiceParameterPage(Long serviceOid) throws IBToolsException{
		
		String parameters = generateParameterLink(false, "", "", "", "", serviceOid, true, true, "", "", 0L, 0L, "ADD", false);
		String postUrl = postUrl(null, "https://kysprd.finansbank.com.tr/PRD/dep/" + SERVICE_PARAMETER_URL,
				parameters, true)
				.replaceAll("/PRD/dep", "https://kysprd.finansbank.com.tr/PRD/dep")
				.replaceAll("/images", "https://kysprd.finansbank.com.tr/images");
		int htmlIndex = postUrl.indexOf("<HTML");
		if(htmlIndex == -1)
			htmlIndex = postUrl.indexOf("<html");
		postUrl = postUrl.substring(htmlIndex);
		int formStartIndex = postUrl.indexOf("<FORM");
		if(formStartIndex == -1)
			formStartIndex = postUrl.indexOf("<form");
		int formEndIndex = postUrl.lastIndexOf("</form>");
		if(formEndIndex == -1)
			formEndIndex = postUrl.lastIndexOf("</FORM>");
		String subString = postUrl.substring(formStartIndex, formEndIndex + 7);
		postUrl = postUrl.replaceAll(subString, "");
		return postUrl;
	}
	
	public static List<String> getDeployEntryVersionList(String platform, String deployenv, String sourcedb, String componentOid) throws IBToolsException{
		String parameters = generateDeployVersionsLink(platform, deployenv, sourcedb, componentOid);
		String postUrl = postUrl(null, "https://kysprd.finansbank.com.tr/PRD/dep/" + DELPOY_ENTRY_VERSION_URL,
				parameters, true);
		String selectString = postUrl.substring(postUrl.indexOf("name=p_version>") + 15 );
		selectString = selectString.substring(0 , selectString.indexOf("</select></TD>"));
		
		List<String> optionList = InputAnalyzer.getOptionValues(selectString);
		return optionList;
	}

	public static String getDeployTableReleasesAndProjectPage(KysTableDeployInfo info) throws IBToolsException{
		String parameters = generateTableTransferLink(info, "ADD");
		String postUrl = postUrl(null, "https://kysprd.finansbank.com.tr/PRD/dep/" + TABLE_DEPLOY_URL,
				parameters, true);
		return postUrl;
	}
	public static List<SelectItem> getDeployTableReleases(String postUrl) throws IBToolsException{
		String selectString = postUrl.substring(postUrl.indexOf("name=\"p_release\"") + 19);
		selectString = selectString.substring(0 , selectString.indexOf("</select></td>"));
		
		List<SelectItem> optionList = InputAnalyzer.getOptionLabelAndValues(selectString);
		return optionList;
	}
	public static List<SelectItem> getDeployTableProjects(String postUrl) throws IBToolsException{
		String selectString = postUrl.substring(postUrl.indexOf("onchange=\"GetProjectTransitions()\" >") + 38);
		selectString = selectString.substring(0 , selectString.indexOf("</select></td>"));
		
		List<SelectItem> optionList = InputAnalyzer.getOptionLabelAndValues(selectString);
		optionList.add(0, new SelectItem("", "-Select-"));
		return optionList;
	}
	public static List<SelectItem> getDeployTableProjectWorkflows(String projectId) throws IBToolsException{
		String parameters = generateProjectRegionLink(projectId);
		String postUrl = postUrl(null, "https://kysprd.finansbank.com.tr/PRD/dep/" + PROJECT_REGION_URL,
				parameters, true);
		
		String selectString = postUrl.substring(postUrl.indexOf("name=\"p_transitionid\">") + 24);
		selectString = selectString.substring(0 , selectString.indexOf("</select></td>"));
		
		List<SelectItem> optionList = InputAnalyzer.getOptionLabelAndValues(selectString);
		return optionList;
	}
	
	public static String[] deployTable(KysTableDeployInfo info) throws IBToolsException{
		String parameters = generateTableTransferLink(info, "SAV");
		String response = postUrl(null, "https://kysprd.finansbank.com.tr/PRD/dep/" + TABLE_DEPLOY_URL,
				parameters, true);
		String[] sonuc = new String[2];
		sonuc[0] = "";
		sonuc[1] = "";
		sonuc[1] = response.toUpperCase().substring(response.indexOf("<html"));
		response = response.toUpperCase().substring(response.indexOf("<CENTER"));
		response = response.toUpperCase().substring(0, response.indexOf("</CENTER>") + 9);
		sonuc[0] = response;
		return sonuc;
	}
	public static String saveQueryParameter(String iotype, String paramName, String preText, String postText, 
			String paramType, String order, String defaultValue, String id, 
			String mainId, boolean pureHtmlResponse) throws IBToolsException{
		
		String parameters = generateQueryParameterLink(iotype, paramName, preText, postText, 
				paramType, order, defaultValue, "SAV", id, 
				mainId, pureHtmlResponse);
		

		return postUrl("", "https://kysprd.finansbank.com.tr/PRD/dep/" + QUERY_PARAMETER_URL,
				parameters, pureHtmlResponse);
	}

	public static String deployComponent(String componentoid, String version, String description, boolean pureHtmlResponse)	throws IBToolsException{
		
		String parameters = generateDeployComponentLink(componentoid, version, description, pureHtmlResponse);
		
		return postUrl("", "https://kysprd.finansbank.com.tr/PRD/dep/" + COMPONENT_DEPLOY_URL,
				parameters, pureHtmlResponse);
	}
	
	private static String generateQueryParameterLink(String iotype, String paramName, String preText, String postText, 
			String paramType, String order, String defaultValue, String processType, String id, 
			String mainId, boolean pureHtmlResponse){
//		p_paramiotype:I
//		p_parambagkeyname:
//		p_pretext:
//		p_paramname:TABLE
//		p_posttext:
//		p_paramtype:1
//		p_order:1
//		p_defaultvalue:%
//		p_userid:91030202
//		p_process_type:SAV
//		p_id:
//		p_mainid:1881000002905980
		StringBuffer parameters = new StringBuffer();
		parameters.append("p_paramiotype=");
		parameters.append(iotype == null ? "" : iotype);
		parameters.append("&p_parambagkeyname=");
//		parameters.append(bagKeyName == null ? "" : bagKeyName);
		parameters.append("&p_pretext=");
		parameters.append(preText == null ? "" : preText);
		parameters.append("&p_paramname=");
		parameters.append(paramName == null ? "" : paramName);
		parameters.append("&p_posttext=");
		parameters.append(postText == null ? "" : postText);
		parameters.append("&p_paramtype=");
		parameters.append(paramType == null ? "" : paramType);
		parameters.append("&p_order=");
		parameters.append(order == null ? "" : order);
		parameters.append("&p_defaultvalue=");
		parameters.append(defaultValue == null ? "" : defaultValue);
		parameters.append("&p_process_type=");
		parameters.append(processType == null ? "" : processType);
		parameters.append("&p_id=");
		parameters.append(id == null ? "" : id);
		parameters.append("&p_mainid=");
		parameters.append(mainId == null ? "" : mainId);
		parameters.append("&p_userid=91030202");
		
		return parameters.toString();
	}
	
	private static String generateDeployComponentLink(String componentoid, String version, String description, boolean pureHtmlResponse){

		StringBuffer parameters = new StringBuffer();
		parameters.append("p_process_type=SAV&p_userid=91030202&p_id=&p_parentid=1&p_ortam=311&p_release=&p_decomposed=&p_projects=&p_tables=&p_exceptions=~&p_bundles=~&p_eod=0&p_acc=0&p_pot=0&p_app=0&p_mnu=0&p_pro=0&p_ftm=0&p_con=0&p_eod_exp=&p_acc_exp=&p_pot_exp=&p_app_exp=&p_mnu_exp=&p_pro_exp=&p_ftm_exp=&p_con_exp=");
		parameters.append("&p_componentoid=");
		parameters.append(componentoid);
		parameters.append("&p_version=");
		parameters.append(version);
		parameters.append("&p_ack=");
		parameters.append(description);
		
		return parameters.toString();
	}
	
	private static String generateParameterLink(boolean isArray, String businessType, String description,
			String domain, String domainType, Long serviceOid, boolean isInput, boolean isOptional,
			String name, String type, Long parentOid, Long parameterOid, String processType, boolean pureHtmlResponse){

		StringBuffer parameters = new StringBuffer();
		parameters.append("p_arr=");
		parameters.append(isArray ? "1" : "0");
		parameters.append("&p_businesstype=");
		parameters.append(businessType == null ? "" : businessType);
		parameters.append("&p_description=");
		parameters.append(description == null ? "" : description);
		parameters.append("&p_domain=");
		parameters.append(domain == null ? "" : domain);
		parameters.append("&p_domaintype=");
		parameters.append(domainType == null ? "" : domainType);
		parameters.append("&p_id=");
		parameters.append(parameterOid == null || parameterOid < 1L ? "" : parameterOid);
		parameters.append("&p_id_old=");
		parameters.append(parameterOid == null || parameterOid < 1L ? "" : parameterOid);
		parameters.append("&p_iotype=");
		parameters.append(isInput ? "I" : "O");
		parameters.append("&p_mainid=");
		parameters.append(serviceOid == null || serviceOid < 1L ? "" : serviceOid);
		parameters.append("&p_optional=");
		parameters.append(isOptional ? "1" : "0");
		parameters.append("&p_paramname=");
		parameters.append(name);
		parameters.append("&p_paramtype=");
		parameters.append(type);
		parameters.append("&p_parentoid=");
		parameters.append(parentOid == null || parentOid < 1 ? "" : parentOid);
		parameters.append("&p_process_type=");
		parameters.append(processType);
		parameters.append("&p_userid=91030202&p_xsd_encrypted=0&p_xsd_ignorable=0");
		
		return parameters.toString();
	}
	private static String generateServiceLink(String groupName, Long componentOid, Long serviceOid){

		StringBuffer parameters = new StringBuffer();
		parameters.append("p_platform=1");
		parameters.append("&p_version=0");
		parameters.append("&p_deprec=0");
		parameters.append("&p_deleted=0");
		parameters.append("&p_classoid=");
		parameters.append("&p_class_methodoid=");
		parameters.append("&p_servicename=");
		parameters.append("&p_archieve=0");
		parameters.append("&p_extplatform=0");
		parameters.append("&p_ttype=1");
		parameters.append("&p_visible=1");
		parameters.append("&p_channelid=001");
		parameters.append("&p_env=CB");
		parameters.append("&p_servicetype=N");
		parameters.append("&p_service_type=0");
		parameters.append("&p_category=");
		parameters.append("&p_service_scope=0");
		parameters.append("&p_reversal_serviceoid=");
		parameters.append("&p_batch=0");
		parameters.append("&p_operation_type=2");
		parameters.append("&p_process_type=ADD");
		parameters.append("&p_servicehist_id=");
		parameters.append("&p_batch_type=");
		parameters.append("&p_batch_classname=");
		parameters.append("&p_batch_tablename=");
		parameters.append("&p_vbservicename=");
		parameters.append("&p_desc=");
		parameters.append("&p_eski=");
		parameters.append("&p_userid=91030202");
		parameters.append("&p_group=");
		parameters.append(groupName);
		parameters.append("&p_componentoid=");
		parameters.append(componentOid);
		parameters.append("&p_id=");
		parameters.append(serviceOid);
		
		return parameters.toString();
	}
	private static String generateDeployVersionsLink(String platform, String deployenv, String sourcedb,  String componentOid){

		StringBuffer parameters = new StringBuffer();
		parameters.append("p_ortamname=");
		parameters.append(deployenv);
		parameters.append("&p_platform=");
		parameters.append(platform);	//Core=1
		parameters.append("&p_sourcedb=");
		parameters.append(sourcedb);
		parameters.append("&p_userid=91030202&p_componentoid=");
		parameters.append(componentOid);
		parameters.append("&p_release=");
		
		return parameters.toString();
	}
	
	private static String generateTableTransferLink(KysTableDeployInfo info, String operation){

		StringBuffer parameters = new StringBuffer();
		parameters.append("p_ack=");
		parameters.append("(" + SessionManager.getLoginUserCode() + ") " + info.getAck());//SMS Mimari prj kapsaminda
		parameters.append("&p_db1=");
		parameters.append(info.getFromSrv());//cbtst01
		parameters.append("&p_db2=");
		parameters.append(info.getToSrv());//cbuat01 , cbpre01, cbprd01
		parameters.append("&p_id=");
		parameters.append("&p_platform1=");
		parameters.append(info.getFromPlatform());//CORE=1
		parameters.append("&p_platform2=");
		parameters.append(info.getToPlatform());//CORE=1
		parameters.append("&p_process_type=");
		parameters.append(operation);//SAV, ADD
		parameters.append("&p_projects=");
		parameters.append(info.projectFlowStr());//100089%24150905762~100089%24150908283~
		parameters.append("&p_release=");
		if(info.getRelease() != null && !info.getRelease().equalsIgnoreCase(Globals.EMPTY_STRING))
			parameters.append(info.getRelease());//Emergency=1
		parameters.append("&p_scenarioname=&p_schema_name=");
		parameters.append(info.getSchema());//PRM
		parameters.append("&p_table_name=");
		parameters.append(info.getTable());//PRM_CMPGN_FORBIDDENCHANNELREL
		parameters.append("&p_transfer=");
		parameters.append(info.getTransferType());//1-Empty 2-Full 3-Structure Only 4-Other
		parameters.append("&p_tur=1&p_userid=91030202");
		
		return parameters.toString();
	}

	private static String generateProjectRegionLink(String projectId){

		StringBuffer parameters = new StringBuffer();
		parameters.append("p_region_name=Projects");
		parameters.append("&p_form=dataForm");
		parameters.append("&p_target_form=submitForm");
		parameters.append("&p_target_paramname=p_projects");
		parameters.append("&p_userid=91030202");
		parameters.append("&p_releasetype=");
		parameters.append("&p_project_transitions=");
		parameters.append("&p_projectid=");
		parameters.append(projectId);
		parameters.append("&p_transitionid=0");
		
		return parameters.toString();
	}
	
	
	private static void login() throws IBToolsException{
		
		HttpURLConnection hConnection = null;
		PrintStream ps = null;
		
		try {
			URL url = new URL("https://kysprd.finansbank.com.tr/PRD/dep/" + LOGIN_URL);
			
			hConnection = (HttpURLConnection) url
					.openConnection();
			
			hConnection.setDoOutput(true);
			hConnection.setRequestMethod("POST");
	
			StringBuffer parameters = new StringBuffer();
			parameters.append("P_USERNAME=");
			parameters.append("T32556");
			parameters.append("&P_PASSWORD=");
			parameters.append("Diren131313");
			
			ps = new PrintStream(hConnection.getOutputStream());
			ps.print(parameters);
			ps.close();
	
			hConnection.connect();
			String cookie = hConnection.getHeaderField("Set-Cookie");
			SessionManager.setSessionCookieKYS(cookie);
			
			InputStream in = hConnection.getInputStream();
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			StringBuilder sb = new StringBuilder();
			String line = null;
			while ((line = br.readLine()) != null) {
				sb.append(line + "\n");
			}
			if(in != null)
				in.close();
			String response = sb.toString();
			if(response.indexOf("User name or password is invalid") > -1){
				response = "User name or password is invalid. <br>ERROR LDAP Connection can not be established.";
			}
			else {
				response = "Login successful";
			}
			System.out.print("\n---------------------------------\n");
			System.out.print("-- " + response);
			System.out.print("\n---------------------------------\n");
			
		} catch (MalformedURLException e) {
			throw new IBToolsException(new ExceptionStringHandler(true, "KYS connection url malformed"));
		} catch (IOException e) {
			throw new IBToolsException(new ExceptionStringHandler(true, "KYS connection unsuccessful"));
		} finally {
			if(hConnection != null)
				hConnection.disconnect();
		}
	}
	
	public static String postUrl(String bagKey, String urlStr, String parameters, boolean pureHtmlResponse) throws IBToolsException {
		
		HttpURLConnection hConnection = null;
		PrintStream ps = null;
		String response = null;
		
		try {
			URL url = new URL(urlStr);
			
			hConnection = (HttpURLConnection) url
					.openConnection();
			
			hConnection.setDoOutput(true);
			String cookie = SessionManager.getSessionCookieKYS();
			if(cookie == null){
				login();
				cookie = SessionManager.getSessionCookieKYS();
			}
			hConnection.setRequestProperty("Cookie", cookie );
			hConnection.setRequestMethod("POST");
			
			ps = new PrintStream(hConnection.getOutputStream());
			ps.print(parameters);
			ps.close();
			
			hConnection.connect();
			
			InputStream in = hConnection.getInputStream();
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			StringBuilder sb = new StringBuilder();
			String line = null;
			while ((line = br.readLine()) != null) {
				sb.append(line + "\n");
			}
			if(in != null)
				in.close();
			
			String sonuc = sb.toString();
			if(pureHtmlResponse){
				response = sonuc;
			}
			else {
				if(sonuc.indexOf("This record has.") > -1){
					throw new IBToolsException(new ExceptionStringHandler(false, "This record has already been defined. To update the record please select from the list."));
				} else if(sonuc.indexOf("Parameter must be defined as a bagkey!") > -1){
					throw new IBToolsException(new ExceptionStringHandler(false, bagKey + " : Parameter must be defined as a bagkey!"));
				} else if(sonuc.indexOf("This record has already been defined") > -1){
					throw new IBToolsException(new ExceptionStringHandler(false, bagKey + " : This record has already been defined!"));
				}
				if(sonuc.indexOf("platform.</P>") > -1){
					response = sonuc.substring(
							sonuc.indexOf("<P class=\"hata\">") + 16, 
							sonuc.indexOf("platform.</P>") + 9)
							.replaceAll("<br>", "\n")
							.replaceAll("<BR>", "\n")
							.replaceAll("Name=", "Name=<span style='font-weight:bold'>")
							.replaceAll("Record has been", "</span>Record has been");
					System.out.print("-- " + response);
					System.out.print("\n---------------------------------\n");
				}
				else {
					System.out.print("-- Response cannot be parsed");
					System.out.print("\n---------------------------------\n");
				}
				
			}
		} catch (MalformedURLException e) {
			throw new IBToolsException(new ExceptionStringHandler(true, "KYS connection url malformed"));
		} catch (IOException e) {
			throw new IBToolsException(new ExceptionStringHandler(true, "KYS connection unsuccessful(" + urlStr + "?" + parameters + ")"));
		} finally {
			if(hConnection != null)
				hConnection.disconnect();
		}
		return response;
	}
	
	public static String postTestUrl() throws IBToolsException{
		
		HttpURLConnection hConnection = null;
		String response = "";
		
		try {
			URL url = new URL("https://kysprd.finansbank.com.tr/PRD/dep/" + SERVICE_PARAMETER_URL);
			
			hConnection = (HttpURLConnection) url
					.openConnection();
			
			hConnection.setDoOutput(true);
			String cookie = SessionManager.getSessionCookieKYS();
			if(cookie == null){
				login();
				cookie = SessionManager.getSessionCookieKYS();
			}
			hConnection.setRequestProperty("Cookie", cookie );
			hConnection.setRequestMethod("POST");
	
			hConnection.connect();
			
			InputStream in = hConnection.getInputStream();
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			StringBuilder sb = new StringBuilder();
			String line = null;
			while ((line = br.readLine()) != null) {
				sb.append(line + "\n");
			}
			if(in != null)
				in.close();
			response = sb.toString();
			
		} catch (MalformedURLException e) {
			throw new IBToolsException(new ExceptionStringHandler(true, "KYS connection url malformed"));
		} catch (IOException e) {
			throw new IBToolsException(new ExceptionStringHandler(true, "KYS connection unsuccessful"));
		} finally {
			if(hConnection != null)
				hConnection.disconnect();
		}
		return response;
	}
	
	
	@SuppressWarnings("unchecked")
	public static List<DadServiceParametersDAO> loadKYSParentParameters(String parentKeyName, long serviceOid) throws IBToolsException{
		Session session = HibernateUtil.getDEVSession();
	    Transaction tx = null;
	    List<DadServiceParametersDAO> result = null;
	    
	    try {
			tx = HibernateUtil.beginDEVTransaction(session);
			Criteria criteria = session.createCriteria(DadServiceParametersDAO.class);
    		criteria.add(Restrictions.eq("serviceoid", serviceOid));
    		if( !parentKeyName.equalsIgnoreCase(Globals.EMPTY_STRING))
    			criteria.add(Restrictions.eq("name", parentKeyName));
    		criteria.add(Restrictions.eq("status", true));
    		criteria.add(Restrictions.eq("type", Globals.PARAM_TABLE));
    		result = (List<DadServiceParametersDAO>) criteria.list();
			tx.commit();
			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		}
	    return result;
	}
	
	@SuppressWarnings("unchecked")
	public static DadServiceParametersDAO loadKYSParameter(String parameterName, long serviceOid) throws IBToolsException{
		Session session = HibernateUtil.getDEVSession();
	    Transaction tx = null;
	    DadServiceParametersDAO result = null;
	    
	    try {
			tx = HibernateUtil.beginDEVTransaction(session);
			Criteria criteria = session.createCriteria(DadServiceParametersDAO.class);
    		criteria.add(Restrictions.eq("serviceoid", serviceOid));
    		if( !parameterName.equalsIgnoreCase(Globals.EMPTY_STRING))
    			criteria.add(Restrictions.eq("name", parameterName));
    		criteria.add(Restrictions.eq("status", true));
    		List<DadServiceParametersDAO> resultList = (List<DadServiceParametersDAO>) criteria.list();
    		if(resultList.size() > 0){
    			result = resultList.get(0);
    		}
			tx.commit();
			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		}
	    return result;
	}
	
	@SuppressWarnings("unchecked")
	public static List<DadServiceParametersDAO> loadKYSChildrenParameters(long parentOid) throws IBToolsException{
		Session session = HibernateUtil.getDEVSession();
		Transaction tx = null;
		List<DadServiceParametersDAO> selectedServiceTableParameters = null;
		
		try {
			tx = HibernateUtil.beginDEVTransaction(session);
			selectedServiceTableParameters = session.createCriteria(DadServiceParametersDAO.class)
					.add(Restrictions.eq("status", true))
					.add(Restrictions.eq("parentoid", parentOid))
					.list();
			tx.commit();
			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		}
		return selectedServiceTableParameters;
	}
}
